package com.training.java.core.homework08B;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class LaptopDAOJDBCImpl implements LaptopDAO {

	private String jdbcURL;
	private String username;
	private String password;

	public LaptopDAOJDBCImpl(String jdbcURL, String username, String password) {
		this.jdbcURL = jdbcURL;
		this.username = username;
		this.password = password;
	}

	@Override
	public List<Laptop> getAllLaptops() throws DAOException {

		List<Laptop> laptops = new ArrayList<Laptop>();

		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			connection = getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from laptop");

			while (resultSet.next()) {
				long model = resultSet.getLong("MODEL");
				String company = resultSet.getString("COMPANY");
				String os = resultSet.getString("OS");
				String processor = resultSet.getString("PROCESSOR");

				Laptop laptop = new Laptop(model, company, os, processor);

				laptops.add(laptop);
			}
		} catch (SQLException ex) {
			throw new DAOException(ex);
		} finally {
			closeResources(resultSet, statement, connection);
		}

		return laptops;
	}
	
	@Override
	public List<LaptopJoinOwner> getAllLaptopJoinOwners() throws DAOException {

		List<LaptopJoinOwner> laptopjoinowners = new ArrayList<LaptopJoinOwner>();

		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			connection = getConnection();
			statement = connection.createStatement();
			resultSet = statement
					.executeQuery("select a.model model, a.company company, a.os os, a.processor processor, b.cell_number cellnumber from laptop a join laptop_owners b on a.model = b.model");

			while (resultSet.next()) {
				long model = resultSet.getLong("MODEL");
				String company = resultSet.getString("COMPANY");
				String os = resultSet.getString("OS");
				String processor = resultSet.getString("PROCESSOR");
				long cellnumber = resultSet.getLong("CELLNUMBER");;

				LaptopJoinOwner laptopjoinowner = new LaptopJoinOwner(model, company, os, processor, cellnumber);

				laptopjoinowners.add(laptopjoinowner);
			}
		} catch (SQLException ex) {
			throw new DAOException(ex);
		} finally {
			closeResources(resultSet, statement, connection);
		}

		return laptopjoinowners;
	}

	@Override
	public List<LaptopJoinOwner> getAllLaptopJoinOwnersPrep() throws DAOException {

		List<LaptopJoinOwner> laptopjoinowners = new ArrayList<LaptopJoinOwner>();

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {
			connection = getConnection();
			statement = connection.prepareStatement("select a.model model, a.company company, a.os os, a.processor processor, b.cell_number cellnumber from laptop a join laptop_owners b on a.model = b.model");
			resultSet = statement.executeQuery();

			while (resultSet.next()) {
				long model = resultSet.getLong("MODEL");
				String company = resultSet.getString("COMPANY");
				String os = resultSet.getString("OS");
				String processor = resultSet.getString("PROCESSOR");
				long cellnumber = resultSet.getLong("CELLNUMBER");;

				LaptopJoinOwner laptopjoinowner = new LaptopJoinOwner(model, company, os, processor, cellnumber);

				laptopjoinowners.add(laptopjoinowner);
			}
		} catch (SQLException ex) {
			throw new DAOException(ex);
		} finally {
			closeResources(resultSet, statement, connection);
		}

		return laptopjoinowners;
	}

	@Override
	public List<Laptopowner> getAllLaptopowners() throws DAOException {

		List<Laptopowner> laptopowners = new ArrayList<Laptopowner>();

		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			connection = getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from laptop_owners");

			while (resultSet.next()) {
				long cellnumber = resultSet.getLong("CELL_NUMBER");
				long model = resultSet.getLong("MODEL");

				Laptopowner laptopowner = new Laptopowner(cellnumber, model);

				laptopowners.add(laptopowner);
			}
		} catch (SQLException ex) {
			throw new DAOException(ex);
		} finally {
			closeResources(resultSet, statement, connection);
		}

		return laptopowners;
	}

	@Override
	public void addLaptop(Laptop laptop) throws DAOException {

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {
			connection = getConnection();

			long model = laptop.getModel();
			String company = laptop.getCompany();
			String os = laptop.getOs();
			String processor = laptop.getProcessor();

			statement = connection
					.prepareStatement("insert into laptop values(?, ?, ?, ?)");

			statement.setLong(1, model);
			statement.setString(2, company);
			statement.setString(3, os);
			statement.setString(4, processor);

			int numberOfRowsInserted = statement.executeUpdate();
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new DAOException(ex);
		} finally {
			closeResources(resultSet, statement, connection);
		}
	}

	@Override
	public void addLaptopOwner(Laptopowner laptopowner) throws DAOException {

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {
			connection = getConnection();

			long cellnumber = laptopowner.getCellnumber();
			long model = laptopowner.getModel();

			statement = connection
					.prepareStatement("insert into laptop_owners values(?, ?)");

			statement.setLong(1, cellnumber);
			statement.setLong(2, model);

			int numberOfRowsInserted = statement.executeUpdate();
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new DAOException(ex);
		} finally {
			closeResources(resultSet, statement, connection);
		}
	}

	
	/**
	 * @Override public void updateLaptop(Laptop book) throws DAOException {
	 *           Connection connection = null; PreparedStatement statement =
	 *           null; ResultSet resultSet = null;
	 * 
	 *           try { connection = getConnection(); int id = book.getId();
	 *           String author = book.getAuthor(); String isbn = book.getIsbn();
	 *           String title = book.getTitle(); int pages = book.getPages();
	 *           java.sql.Date publisherDate = new
	 *           java.sql.Date(book.getPublisherDate().getTime());
	 * 
	 *           statement = connection.prepareStatement(
	 *           "update book set title = ?, author = ?, number_of_pages = ?, isbn = ?, pub_date = ? where book_id = ?"
	 *           );
	 * 
	 *           statement.setString(1, title); statement.setString(2, author);
	 *           statement.setInt(3, pages); statement.setString(4, isbn);
	 *           statement.setDate(5, publisherDate);
	 * 
	 *           statement.setInt(6, id);
	 * 
	 *           int numberOfRowsUpdate = statement.executeUpdate();
	 * 
	 *           if(numberOfRowsUpdate != 1) { throw new
	 *           DAOException("Update book failed!!"); } } catch(SQLException
	 *           ex) { ex.printStackTrace(); throw new DAOException(ex); }
	 *           finally { closeResources(resultSet, statement, connection); } }
	 * @Override public void deleteLaptop(int bookId) throws DAOException {
	 * 
	 *           Connection connection = null; PreparedStatement statement =
	 *           null; ResultSet resultSet = null;
	 * 
	 *           try { connection = getConnection();
	 * 
	 *           statement =
	 *           connection.prepareStatement("delete book where book_id = ?");
	 *           statement.setInt(1, bookId);
	 * 
	 *           int numberOfRowsDeleted = statement.executeUpdate();
	 * 
	 *           if(numberOfRowsDeleted != 1) { throw new
	 *           DAOException("Delete book failed!!"); } } catch(SQLException
	 *           ex) { ex.printStackTrace(); throw new DAOException(ex); }
	 *           finally { closeResources(resultSet, statement, connection); } }
	 * @Override public Laptop findByPrimaryKey(int bookId) throws DAOException
	 *           {
	 * 
	 *           Connection connection = null; PreparedStatement statement =
	 *           null; ResultSet resultSet = null;
	 * 
	 *           try { connection = getConnection(); statement =
	 *           connection.prepareStatement
	 *           ("select * from book where book_id = ?");
	 * 
	 *           statement.setInt(1, bookId);
	 * 
	 *           resultSet = statement.executeQuery();
	 * 
	 *           if(resultSet.next()) { int id = resultSet.getInt("book_id");
	 *           String title = resultSet.getString("title"); String author =
	 *           resultSet.getString("author"); int pages =
	 *           resultSet.getInt("number_of_pages"); String isbn =
	 *           resultSet.getString("isbn"); Date publisherDate =
	 *           resultSet.getDate("pub_date");
	 * 
	 *           return new Laptop(id, title, author, isbn, pages,
	 *           publisherDate); }
	 * 
	 *           throw new DAOException("Invalid book id provided!!"); }
	 *           catch(SQLException ex) { throw new DAOException(ex); } finally
	 *           { closeResources(resultSet, statement, connection); } }
	 **/

	private Connection getConnection() throws DAOException {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			return DriverManager.getConnection(jdbcURL, username, password);
		} catch (ClassNotFoundException ex) {
			throw new DAOException(ex);
		} catch (SQLException ex) {
			throw new DAOException(ex);
		}

	}

	private void closeResources(ResultSet resultSet, Statement statement,
			Connection connection) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}
